package org.example.atry.pqdao;



import org.example.atry.dbutil.Dbutil;
import org.example.atry.util.Users;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class paqdao {
    //单行查询
    public Users queryone(Integer id){
        Connection cn = Dbutil.getCon();
        String sql = "select * from result where id=?";
        try {
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet cs = ps.executeQuery();
            if(cs.next()){
                Users s = new Users(
                        cs.getInt("id"),
                        cs.getString("name"),
                        cs.getString("charst"));
                return s;
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutil.closeConn();
        }
        return null;
    }
public  List<Users>querysomesp(String name){
    List<Users> ls = new ArrayList<Users>();

    Connection cn = Dbutil.getCon();
    try {
        if(name.equals("粳")) {
            String sql = " select* from result where name like ? ; ";
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, "%"+'粳'+"%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Users pp = new Users(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("charst")
                );
                ls.add(pp);
            }
        }else if(name.equals("籼")){
            String sql = " select* from result where (name not like'%粳%') ; ";
            PreparedStatement ps = cn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Users pp = new Users(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("charst")
                );
                ls.add(pp);
            }
        }else {
            paqdao pp = new paqdao();
            List<Users> users=pp.queryall();
            return users;
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        Dbutil.closeConn();
    }

    return ls;
}
//查询所有
    public List<Users>queryall() {
        List<Users> ls = new ArrayList<Users>();
        Connection cn = Dbutil.getCon();
        try {
            String select = "select * from result";
            PreparedStatement preparedStatement = cn.prepareStatement(select);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Users pp = new Users(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("charst")
                );
                ls.add(pp);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutil.closeConn();
        }

        return ls;
    }

public List<Users> pageall(int pagaNum, int pagaSize) {
    Connection cn = Dbutil.getCon();
//String sql="select * from result where name=? limit (pageNum-1)*pageSize)";
    List<Users> ls = new ArrayList<Users>();
    String select = "select * from result  limit ?,?";
    try {

        PreparedStatement ps = cn.prepareStatement(select);
        ps.setInt(1,(pagaNum-1)*pagaSize);
        ps.setInt(2, pagaSize);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //将查询结果的每行数据 封装为对象 并加入到集合内
            ls.add (new Users(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("charst")
            ));

        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        Dbutil.closeConn();
    }

    return ls;
}
/*public int getTotal(String name) {
        Connection cn = Dbutil.getCon();
}

 */
public List<Users> pagesome(String name, int pagaNum, int pagaSize){
    Connection cn = Dbutil.getCon();
//String sql="select * from result where name=? limit (pageNum-1)*pageSize)";
    List<Users> ls = new ArrayList<Users>();
    //String select = "select * from result   limit 0,10";
    try {
        if(name.equals("粳")) {
            String cd = " select* from result where (name like ?) limit ?,?; ";
            PreparedStatement ps = cn.prepareStatement(cd);
            ps.setString(1, "%"+'粳'+"%");
            ps.setInt(2,(pagaNum-1)*pagaSize);
            ps.setInt(3, pagaSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Users pp = new Users(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("charst")
                );
                ls.add(pp);
            }
        }else if(name.equals("籼")){
            String cd = " select* from result where (name not like ?) limit ?,?; ";
            PreparedStatement ps = cn.prepareStatement(cd);
            ps.setString(1, "%"+'粳'+"%");
            ps.setInt(2,(pagaNum-1)*pagaSize);
            ps.setInt(3, pagaSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                //将查询结果的每行数据 封装为对象 并加入到集合内
                Users pp = new Users(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("charst")
                );
                ls.add(pp);
            }
        }else {
            paqdao pp = new paqdao();
            List<Users> users=pp.pageall(pagaNum, pagaSize);
            return users;
        }
    }
    catch (SQLException e) {
        e.printStackTrace();
    } finally {
        Dbutil.closeConn();
    }

    return ls;
}

public int getTotal() {
    Connection cn = Dbutil.getCon();
//String sql="select * from result where name=? limit (pageNum-1)*pageSize)";

    try {
        String select = "select * from result ";
        PreparedStatement ps = cn.prepareStatement(select);

        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            //将查询结果的每行数据 封装为对象 并加入到集合内
     count=count+1;

        }
        return count;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        Dbutil.closeConn();
    }

}
    public int geTotal(String name) {
        Connection cn = Dbutil.getCon();
//String sql="select * from result where name=? limit (pageNum-1)*pageSize)";

        try {
            if(name.equals("粳")) {
                String select = "select * from result where name like ? ; ";
                PreparedStatement ps = cn.prepareStatement(select);
                ps.setString(1, "%"+'粳'+"%");



                ResultSet rs = ps.executeQuery();
                int count = 0;
                while (rs.next()) {
                    //将查询结果的每行数据 封装为对象 并加入到集合内
                    count = count + 1;

                }
                return count;
            }else{
                String select = "select * from result where name not like ? ; ";
                PreparedStatement ps = cn.prepareStatement(select);
                ps.setString(1, "%"+'粳'+"%");



                ResultSet rs = ps.executeQuery();
                int count = 0;
                while (rs.next()) {
                    //将查询结果的每行数据 封装为对象 并加入到集合内
                    count = count + 1;

                }
                return count;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutil.closeConn();
        }

    }
}
